/**
 * 测试时区处理脚本
 * 用于验证数据库时间和系统时间的一致性
 */

const { query } = require('../config/db');

async function testTimeZone() {
  try {
    console.log('🕐 开始测试时区处理...\n');

    // 1. 测试系统时间
    const systemTime = new Date();
    console.log('📅 系统时间 (本地):', systemTime.toLocaleString('zh-CN', { timeZone: 'Asia/Shanghai' }));
    console.log('📅 系统时间 (UTC):', systemTime.toISOString());
    console.log('📅 系统时间 (时间戳):', systemTime.getTime());
    console.log('');

    // 2. 测试数据库时间
    const dbTimeResult1 = await query('SELECT NOW() as utc_time');
    const dbTimeResult2 = await query("SELECT CONVERT_TZ(NOW(), '+00:00', '+08:00') as beijing_time");
    const dbTimeResult3 = await query('SELECT UNIX_TIMESTAMP(NOW()) as timestamp');

    console.log('🗄️ 数据库时间:');
    console.log('   UTC 时间:', dbTimeResult1[0].utc_time);
    console.log('   北京时间:', dbTimeResult2[0].beijing_time);
    console.log('   时间戳:', dbTimeResult3[0].timestamp);
    console.log('');

    // 3. 测试订单时间查询
    const orderTimeQuery = `
      SELECT 
        id,
        order_number,
        status,
        planned_start_time,
        planned_end_time,
        CONVERT_TZ(planned_start_time, '+00:00', '+08:00') as planned_start_beijing,
        CONVERT_TZ(planned_end_time, '+00:00', '+08:00') as planned_end_beijing,
        actual_start_time,
        actual_end_time,
        CONVERT_TZ(actual_start_time, '+00:00', '+08:00') as actual_start_beijing,
        CONVERT_TZ(actual_end_time, '+00:00', '+08:00') as actual_end_beijing
      FROM orders 
      WHERE status IN ('in_progress', 'completed')
      ORDER BY created_at DESC 
      LIMIT 5
    `;

    const orders = await query(orderTimeQuery);
    
    console.log('📋 最近5个订单的时间信息:');
    orders.forEach((order, index) => {
      console.log(`\n订单 ${index + 1}: ${order.order_number} (${order.status})`);
      console.log(`   预计入场: ${order.planned_start_time} -> ${order.planned_start_beijing}`);
      console.log(`   预计离场: ${order.planned_end_time} -> ${order.planned_end_beijing}`);
      if (order.actual_start_time) {
        console.log(`   实际入场: ${order.actual_start_time} -> ${order.actual_start_beijing}`);
      }
      if (order.actual_end_time) {
        console.log(`   实际离场: ${order.actual_end_time} -> ${order.actual_end_beijing}`);
      }
    });

    // 4. 测试自动完成订单的查询条件
    console.log('\n🔍 测试自动完成订单的查询条件:');
    
    const expiredOrdersQuery = `
      SELECT
        id,
        order_number,
        status,
        CONVERT_TZ(planned_end_time, '+00:00', '+08:00') as planned_end_beijing,
        CONVERT_TZ(NOW(), '+00:00', '+08:00') as current_beijing_time,
        CASE 
          WHEN CONVERT_TZ(planned_end_time, '+00:00', '+08:00') < CONVERT_TZ(NOW(), '+00:00', '+08:00') 
          THEN '已超时' 
          ELSE '未超时' 
        END as timeout_status
      FROM orders
      WHERE status = 'in_progress'
        AND actual_end_time IS NULL
      ORDER BY planned_end_time DESC
      LIMIT 10
    `;

    const expiredOrders = await query(expiredOrdersQuery);
    
    if (expiredOrders.length > 0) {
      console.log('进行中的订单状态:');
      expiredOrders.forEach((order, index) => {
        console.log(`${index + 1}. ${order.order_number}`);
        console.log(`   预计离场: ${order.planned_end_beijing}`);
        console.log(`   当前时间: ${order.current_beijing_time}`);
        console.log(`   状态: ${order.timeout_status}`);
        console.log('');
      });
    } else {
      console.log('没有进行中的订单');
    }

    console.log('✅ 时区测试完成');

  } catch (error) {
    console.error('❌ 时区测试失败:', error);
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  testTimeZone().then(() => {
    process.exit(0);
  }).catch(error => {
    console.error('脚本执行失败:', error);
    process.exit(1);
  });
}

module.exports = { testTimeZone };
